package com.hz.ys_statistics.dao;



import javax.servlet.http.HttpServletRequest;

import com.hz.framework.MyInterFace.SystemInterFace;
import com.hz.framework.dao.CommonDao;
import com.hz.framework.util.DateUtil;

/**
 * *****************************************************************************
 *               Copyright(c) 2010 by 哈尔滨华泽数码科技有限公司.
 *                       All rights reserved.
 *******************************************************************************
 *     File Name         :  com.hz.ys_stock.dao.PurchaseDao.java
 *     Description(说明)	:  供应商管理
 * -----------------------------------------------------------------------------
 *     No.        Date              Revised by           Description	  
 *     1		Jun 8, 2011				韩飞	              Created
 *****************************************************************************
 */
public class StatisticsDao implements SystemInterFace{
	
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @return 
	 * 方法功能: 统计进货信息
	 */
	public String SelPurchase(HttpServletRequest request){
		
		String year = CommonDao.isnull(request.getParameter("year")); year = "".equals(year)?DateUtil.getYear():year;
		String month = CommonDao.isnull(request.getParameter("month"));
		String where = " where 1=1 ";
		String row = "";
		String group = "";
		if(!"".equals(month)){
			group = "p.orderdate";
			row = DateUtil.isLeapYear(year,month.replace("-", ""));
		}else{
			group = "subString(p.orderdate,1,7)";
			row = "12";
		}
		request.setAttribute("year", year);
		request.setAttribute("month", month);
		request.setAttribute("row", row);
		
		String left = "select b.goodsid,sum(sumgoods) as sumgoods,sum(summoney) as summoney,"+group+" as orderdate from pres_buymak as b " +
				"left join pres_purchase as p on b.purchaseid=p.purchaseid and p.progress = '到货' and p.orderdate like '"+year+month+"-"+"%' " +
				" where p.progress = '到货' and p.orderdate like '"+year+month+"-"+"%'  group by b.goodsid,"+group;
		
		if(!"".equals(CommonDao.isnull(request.getParameter("goodsname")))){ //物品名称
			where += " and m.goodsname like '%"+ request.getParameter("goodsname")+"%'";
			request.setAttribute("goodsname", request.getParameter("goodsname"));
		}
//		分解设定的查询条件 是否有对应查询
		StringBuilder sbSQL = new StringBuilder();
		sbSQL.append("select m.goodsname,b.goodsid");//查询默认字段
		for(int i=1;i<=Integer.parseInt(row);i++){
			sbSQL.append(",row"+i+".sumgoods as god"+i);
			sbSQL.append(",row"+i+".summoney as mon"+i);
		}
		//查询主表
		sbSQL.append(" from pres_buymak as b ");
		sbSQL.append(" left join pres_makings as m on b.goodsid = m.goodsid ");
		
		for(int i=1;i<=Integer.parseInt(row);i++){
			sbSQL.append(" left join ("+left+") as row"+i+" on row"+i+".goodsid = b.goodsid and row"+i+".orderdate = '"+year+month+"-"+(i<10?"0"+i:i)+"'");
		}
		sbSQL.append(where); //增加 相关查询条件
		sbSQL.append(" group by b.goodsid ");
		sbSQL.append(" order by b.goodsid");
		System.out.println("+"+sbSQL);
		return sbSQL.toString();
	}
	
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @return 
	 * 方法功能: 收入支出统计
	 */
	public String SelFinancial(HttpServletRequest request){
		
		String year = CommonDao.isnull(request.getParameter("year")); year = "".equals(year)?DateUtil.getYear():year;
		String month = CommonDao.isnull(request.getParameter("month"));
		String where = " where 1=1 ";
		String row = "";
		String group = "";
		if(!"".equals(month)){
			group = "tpdate";
			row = DateUtil.isLeapYear(year,month.replace("-", ""));
		}else{
			group = "subString(tpdate,1,7)";
			row = "12";
		}
		StringBuffer sql = new StringBuffer();
		sql.append("select tp.tpdate ");//查询默认字段
		
		String[] tpflag = {"plan","order","tak","purchase","pay"};
		String field ="";
		for(int i=0;i<tpflag.length;i++){
			field += ","+tpflag[i]+"tab.realmoney as "+tpflag[i]+"sum";
		}
		sql.append(field);
		
		sql.append(" from (select distinct ("+group+") as tpdate from takingsorpay ) tp");//库存中的产品
		String leftjoin ="";
		for(int i=0;i<tpflag.length;i++){
			leftjoin += " left join (select sum(realmoney)as realmoney,"+group+" as tpdate from takingsorpay where tpflag = '"+tpflag[i]+"' and tpdate like '"+year+month+"-"+"%' group by tpflag,"+group+") " +
					" as "+tpflag[i]+"tab on "+tpflag[i]+"tab.tpdate = tp.tpdate ";
		}
		sql.append(leftjoin);//查询默认字段
		sql.append(where); //增加 相关查询条件
		sql.append(" group by tp.tpdate order by tp.tpdate");
		
		request.setAttribute("year", year);
		request.setAttribute("month", month);
		request.setAttribute("row", row);
		request.setAttribute("tpflag", tpflag);
		
		System.out.println("sql+"+sql);
		return sql.toString();
	}
	
	
	/**
	 * @author 韩飞
	 * @time Nov 16, 2011	
	 * @return 
	 * 方法功能: 客户订单统计表
	 */
	public String SelClientOrder(HttpServletRequest request){
		

		String year = CommonDao.isnull(request.getParameter("year")); year = "".equals(year)?DateUtil.getYear():year;
		String month = CommonDao.isnull(request.getParameter("month"));
		String where = " where 1=1 ";
		String row = "";
		String group = "";
		if(!"".equals(month)){
			group = "orderTime";
			row = DateUtil.isLeapYear(year,month.replace("-", ""));
		}else{
			group = "subString(orderTime,1,7)";
			row = "12";
		}
		request.setAttribute("year", year);
		request.setAttribute("month", month);
		request.setAttribute("row", row);
		
		String left = "select clientId,count(clientId) as sumnum,sum(total) as summoney,"+group+" as orderTime " +
					" from orderinfo where orderTime like '"+year+month+"-"+"%' group by clientId,"+group;
		
		if(!"".equals(CommonDao.isnull(request.getParameter("goodsname")))){ //物品名称
			where += " and m.goodsname like '%"+ request.getParameter("goodsname")+"%'";
			request.setAttribute("goodsname", request.getParameter("goodsname"));
		}
//		分解设定的查询条件 是否有对应查询
		StringBuilder sbSQL = new StringBuilder();
		sbSQL.append("select c.clientName,o.clientId");//查询默认字段
		for(int i=1;i<=Integer.parseInt(row);i++){
			sbSQL.append(",row"+i+".sumnum as num"+i);
			sbSQL.append(",row"+i+".summoney as mon"+i);
		}
		//查询主表
		sbSQL.append(" from orderinfo as o ");
		sbSQL.append(" left join clientinfo as c on o.clientId = c.clientId ");
		
		for(int i=1;i<=Integer.parseInt(row);i++){
			sbSQL.append(" left join ("+left+") as row"+i+" on row"+i+".clientId = o.clientId and row"+i+".orderTime = '"+year+month+"-"+(i<10?"0"+i:i)+"'");
		}
		sbSQL.append(where); //增加 相关查询条件
		sbSQL.append(" group by o.clientId ");
		sbSQL.append(" order by o.clientId");
		System.out.println("+"+sbSQL);
		return sbSQL.toString();
	}
}
